//##LICENSE##
/*
 * See License.txt in the source folder for the license.
 */
//##END_LICENSE##

package org.gidoo.owl2.backend;

import java.io.Serializable;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.gidoo.xmldb.ConnectionHelper;
import org.gidoo.xmldb.RestoreXML;

/**
 * An implementation of a DictionaryProvider using a Derby database in network 
 * mode.
 * 
 * @see #init(Properties)
 * @author Thomas Krause
 */
public class DerbyDicProvider implements DictionaryProvider, Serializable
{

  public static final int QUERY_TIMEOUT_SHORT=5;
  public static final int QUERY_TIMEOUT_LONG=15;
  
  transient private Connection connection;
  private Properties env;
  private PreparedStatement psMatchingTerms;
  private PreparedStatement psResultsAsIDs;  
  private PreparedStatement psGetNameFromDocID;
  private PreparedStatement psGetDocIDFromName;
  private PreparedStatement psDocuments;
  
  /**
   * Init the dictionary provider.
   * <br>
   * <b>Properties</b>
   * <ul>
   * <li>dbname - the name of the database</li>
   * </ul>
   * 
   * @param env A set of properties. 
   * 
   */
  public void init(Properties env)
    throws DatabaseError
  {
    
   this.env = env;
      
    try
    {
      if(connection != null && !connection.isClosed())
      {
        connection.close();
        connection = null;
      }
      
      if(this.env.getProperty("dbname") == null)
      {
        this.env.put("dbname", "owl2");
      }
      
      Connection con = getConnection();
      
      // prepare statements
      psResultsAsIDs = con.prepareStatement(
        "select predecbyname(e_t.id,'entry') as id from characters as c, " +
          "elements as e_t, children as child, contents as con\n" +
        "where\n" +
        "e_t.name='orth'\n"+ // select the orth-elements
        "AND c.value_lower like ?\n" +
        "AND c.id=child.idchild \n" +
        "AND e_t.id=child.idparent \n" +
        "AND con.id=c.id \n" +
        "AND con.docID=?");
      
      psMatchingTerms = con.prepareStatement(
        "select c.value as value from characters as c, " +
          "elements as e_t, children as child, contents as con\n" +
        "where\n" +
        "e_t.name='orth'\n"+ // select the orth-elements
        "AND c.value_lower like ?\n" +
        "AND c.id=child.idchild \n" +
        "AND e_t.id=child.idparent\n " +
        "AND con.id=c.id \n" +
        "AND con.docID=?");
      
      psGetDocIDFromName = con.prepareStatement(
        "select id from documents where name=?");
      psGetNameFromDocID = con.prepareStatement(
        "select name from documents where id=?");
      
      psDocuments = con.prepareStatement(
        "select id from documents");
      
    }
    catch(SQLException ex)
    {
      Logger.getLogger(DerbyDicProvider.class.getName()).log(Level.SEVERE, null, ex);
      throw new DatabaseError("Could not init database connection-stuff.");
    }
  }
  
  public List<String> getMatchingSearchTerms(long docID, String search)
    throws DatabaseError
  {
    
    LinkedList<String> result = new LinkedList<String>();
    
    if(getConnection() == null)
    {
      return result;
    }
    try
    {
      
      psMatchingTerms.setString(1, search.toLowerCase() + "%");
      psMatchingTerms.setLong(2, docID);
      psMatchingTerms.setQueryTimeout(QUERY_TIMEOUT_SHORT);
      ResultSet rsMatchingTerms = psMatchingTerms.executeQuery();
      
      while(rsMatchingTerms.next())
      {
        String term = rsMatchingTerms.getString(1);
        result.add(term);
      }
      
      
    }
    catch(SQLException ex)
    {
      Logger.getLogger(DerbyDicProvider.class.getName()).log(Level.SEVERE, null, ex);
      throw new DatabaseError("Could not query matching search terms.");
    }
    
    return result;
  }

  public List<Long> getSearchResult(long docID, String search)
    throws DatabaseError
  {
    LinkedList<Long> result = new LinkedList<Long>();
    
    if(getConnection() == null)
    {
      return result;
    }
    try
    {
      
      psResultsAsIDs.setString(1, search.toLowerCase());
      psResultsAsIDs.setLong(2, docID);
      psResultsAsIDs.setQueryTimeout(QUERY_TIMEOUT_LONG);
      ResultSet rsResults = psResultsAsIDs.executeQuery();
      
      
      while(rsResults.next())
      {
        Long l = rsResults.getLong(1);
        result.add(l);
      }
      
    }
    catch(SQLException ex)
    {
      Logger.getLogger(DerbyDicProvider.class.getName()).log(Level.SEVERE, null, ex);
      throw new DatabaseError("Could not query search result.");
    }
    
    return result;
  }
  
  

  public long getDocumentIDFromName(String name)
    throws DatabaseError
  {
    try
    {
      psGetDocIDFromName.setString(1, name);
      psGetDocIDFromName.setQueryTimeout(QUERY_TIMEOUT_SHORT);
      ResultSet rs = psGetDocIDFromName.executeQuery();
      if(rs.next())
      {
        return rs.getLong(1);
      }
    }
    catch(SQLException ex)
    {
      Logger.getLogger(DerbyDicProvider.class.getName()).log(Level.SEVERE, null, ex);
      throw new DatabaseError("Could not query document ID from name.");
    }
    return -1;
  }

  public String getDocumentNameFromID(long id)
    throws DatabaseError
  {
    try
    {
      psGetNameFromDocID.setLong(1, id);
      psGetNameFromDocID.setQueryTimeout(QUERY_TIMEOUT_SHORT);
      ResultSet rs = psGetNameFromDocID.executeQuery();
      if(rs.next())
      {
        return rs.getString(1);
      }
    }
    catch(SQLException ex)
    {
      Logger.getLogger(DerbyDicProvider.class.getName()).log(Level.SEVERE, null, ex);
      throw new DatabaseError("Could not query document name from ID.");
    }
    return null;
  }

  public List<Long> getDocuments()
    throws DatabaseError
  {
    LinkedList<Long> result = new LinkedList<Long>();
    
    try
    {
      psDocuments.setQueryTimeout(QUERY_TIMEOUT_SHORT);
      ResultSet rs = psDocuments.executeQuery();
      while(rs.next())
      {
        result.add(rs.getLong(1));
      }
    }
    catch(SQLException ex)
    {
      Logger.getLogger(DerbyDicProvider.class.getName()).log(Level.SEVERE, null, ex);
      throw new DatabaseError("Could not query documents from database.");
    }
    
    return result;
  }

  public String getXMLSource(long elementID)
    throws DatabaseError
  {
    Connection conn = getConnection();
    if(conn == null)
    {
      throw new DatabaseError("Could not etablish connection");
    }
    StringWriter result = new StringWriter();

    RestoreXML restore = new RestoreXML(elementID, conn, result);
    restore.startParsing();
    return result.toString();
  }

  protected Connection getConnection()
  {
    if(connection == null)
    {
      connection = ConnectionHelper.connect2DB(this.env.getProperty("dbname"), false);
      
    }
    return connection;
  }
  
  
  
  

}
